package org.example.inventory.dao.impl;



import org.example.inventory.dao.ProductDao;
import org.example.inventory.entity.Product;
import org.example.inventory.exception.InsufficientStockException;
import org.example.inventory.exception.JdbcException;
import org.example.inventory.util.JdbcUtil;
import org.example.inventory.util.ResultSetHandler;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class ProductDaoImpl implements ProductDao {
//    static final ResultSetHandler<Product> handler= rs -> {
//        Product product=new Product();
//        product.setId(rs.getInt("id"));
//        product.setName(rs.getString("name"));
//        product.setPrice(rs.getBigDecimal("price"));
//        product.setStock(rs.getInt("stock"));
//        product.setDescription(rs.getString("description"));
//        product.setCreateTime(rs.getTimestamp("create_time").toLocalDateTime());
//        product.setLastRestockTime(rs.getTimestamp("last_restock_time").toLocalDateTime());
//        return product;
//    };

    private static ResultSetHandler<Product> handler= rs -> {
        try {
            return new Product(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getBigDecimal("price"),
                    rs.getInt("stock"),
                    rs.getString("description"),
                    rs.getTimestamp("create_time").toLocalDateTime(),
                    rs.getTimestamp("last_restock_time")!=null?rs.getTimestamp("last_restock_time").toLocalDateTime():null
            );
        }catch (SQLException e) {
            throw new JdbcException("结果集映射失败",e);
        }
    };

    @Override
    public Product insert(Product product) {
//        List<String> columns = new ArrayList<>(); // 用于存储将要插入的列名
//        List<Object> params = new ArrayList<>(); // 用于存储将要插入的值
//        if (Objects.nonNull(product.getName())) {
//            columns.add("name");
//            params.add(product.getName());
//        }
//        if (Objects.nonNull(product.getPrice())) {
//            columns.add("price");
//            params.add(product.getPrice());
//        }
//        if (Objects.nonNull(product.getStock())) {
//            columns.add("stock");
//            params.add(product.getStock());
//        }
//        if (Objects.nonNull(product.getDescription())) {
//            columns.add("description");
//            params.add(product.getDescription());
//        }
//        if (Objects.nonNull(product.getCreateTime())) {
//            columns.add("create_time");
//            params.add(product.getCreateTime());
//        }
//        if (Objects.nonNull(product.getLastRestockTime())) {
//            columns.add("last_restock_time");
//            params.add(product.getLastRestockTime());
//        }
//        if (columns.isEmpty()) {
//            throw new JdbcException("插入失败：No fields to insert"); // 注意：这里应该是自定义异常，原代码中的JdbcException可能是一个笔误
//        }
//        String sql = "insert into product (" + String.join(", ", columns) + ") " +
//                "values (" + String.join(", ", Collections.nCopies(columns.size(), "?")) + ")";
//        JdbcUtil.executeUpdate(sql, params.toArray());
//        String sql2 = "select * from product where name = ？";
//        return JdbcUtil.executeQuerySingle(sql2,handler,product);
        //插入数据
        String sql="insert into product (name,price,stock,description) values(?,?,?,?)";
        JdbcUtil.executeUpdate(sql,product.getName(),product.getPrice(),product.getStock(),product.getDescription());
        String getLastIdSql="select LAST_INSERT_ID()";
        Integer generatedId= JdbcUtil.executeQuerySingle(getLastIdSql, rs -> rs.getInt(1));
        //填充id并返回
        product.setId(generatedId);
        return product;

    }

    @Override
    public Product selectProductById(Integer id) {
        String sql="select * from product where id=?";
        return JdbcUtil.executeQuerySingle(sql,handler,id);
    }

    @Override
    public int updateProduct(Product product) {
        String sql="update product set name = ?,price = ?,stock = ?,description = ? where id = ?";
        return JdbcUtil.executeUpdate(sql,product.getName(),product.getPrice(),product.getStock(),product.getDescription(),product.getId());
    }

    @Override
    public int deleteProductById(Integer id) {
        String sql="delete from product where id = ?";
        return JdbcUtil.executeUpdate(sql,id);
    }

    @Override
    public List<Product> selectProductList(int pageNum, int pageSize, String sortField, boolean ascending) {
        //参数校验
        if(pageNum<1){
            pageNum=1;
        }
        if(pageSize<1){
            pageSize=10;
        }
        String sortDirection=ascending ? "asc" : "desc";
        //第一个问号是pagesize每页数量 往后查几行 第二个问号是offset偏移量 第几行开始查
        String sql=String.format("select * from product order by %s %s limit ? offset ?",sortField,sortDirection);
        //第一个问号是offset 第二个问号是pagesize
//        String sql=String.format("select * from product order by %s %s limit ? , ?",sortField,sortDirection);
        int offset=(pageNum-1)*pageSize;
        return JdbcUtil.executeQueryList(sql,handler,pageSize,offset);

    }

    @Override
    public List<Product> searchProducts(String nameKeyword, BigDecimal minPrice, BigDecimal maxPrice, Integer minStock) {
            //1.基础SQL
            StringBuilder sql = new StringBuilder("SELECT * FROM product WHERE 1=1");
            //用动态数组存放动态参数
            List<Object> params = new ArrayList<>();

            //2.动态拼接条件
            //名称模糊匹配（忽略空字符串和null）
            if (Objects.nonNull(nameKeyword) && !nameKeyword.isEmpty()) {
                //拼接模糊查询LIKE
                sql.append(" AND name LIKE ?");
                //使用%关键字%实现全模糊
                params.add("%" + nameKeyword + "%");
            }

            //价格区间处理，minPrice和maxPrice都不为空，拼接BETWEEN ? AND ?
            if (minPrice != null && maxPrice != null) {
                //调用compareTo方法比较最低价格和最高价格，如果比较结果>0，说明minPrice>maxPrice
                if (minPrice.compareTo(maxPrice) > 0) {
                    //抛出非法参数异常
                    throw new IllegalArgumentException("最低价格不能大于最高价格");
                }
                //拼接价格区间查询条件
                sql.append(" AND price BETWEEN ? AND ?");
                params.add(minPrice);
                params.add(maxPrice);
            } else {
                //minPrice和maxPrice有一个不为空
                if (minPrice != null) {
                    sql.append(" AND price >= ?");
                    params.add(minPrice);
                }
                if (maxPrice != null) {
                    sql.append(" AND price <= ?");
                    params.add(maxPrice);
                }
            }

            //最小库存过滤（忽略0和负数）
        if (minStock != null && minStock > 0) {
            sql.append(" AND stock >= ?");
            params.add(minStock);
        }

            //3.执行查询，映射查询结果
            return JdbcUtil.executeQueryList(sql.toString(), handler, params.toArray());

    }

    @Override
    public int updateStock(Integer productId, int deltaStock) throws InsufficientStockException {
        //根据ID先查以下商品
        Product product=selectProductById(productId);
        if(product==null){
            throw new JdbcException("商品不存在,id:"+productId);
        }
        //deltaStock<0说明为扣减库存，此时需要进行库存数量判断
        if(deltaStock<0&&product.getStock()<Math.abs(deltaStock)){
            String message=String.format("库存不足，当前库存为%s,扣减库存为%s",product.getStock(),deltaStock);
            throw new InsufficientStockException(message);
        }
        //当deltaStock为负数时，set stock=stock+负数，实则为减库存
        String sql="update product set stock = stock + ? where id = ?";
        return JdbcUtil.executeUpdate(sql,deltaStock,productId);
    }

    @Override
    public List<Product> selectLowStockProducts(int threshold) {
        String sql="select * from product where stock <= ?";
        return JdbcUtil.executeQueryList(sql,handler,threshold);
    }

    @Override
    public long countTotalProducts() {
        String sql="select count(*) from product";
        return JdbcUtil.executeQuerySingle(sql,rs -> rs.getLong(1));
    }

    @Override
    public long countProductsInRangePrice(BigDecimal min, BigDecimal max) {
        if(min.compareTo(max)>0){
            throw new JdbcException("数据不合法");
        }
        String sql="select count(*) from product where price between ? and ?";
        return JdbcUtil.executeQuerySingle(sql,rs->rs.getLong(1),min,max);
    }

    @Override
    public int batchInsertProducts(List<Product> products) {
        int sum=0;
        for (Product product : products) {
            insert(product);
            sum++;
        }
        return sum;
    }

    @Override
    public int updateLastRestockTime(Integer productId, LocalDateTime restockTime) {
        Product product=selectProductById(productId);
        if(product==null){
            throw new JdbcException("商品不存在,id:"+productId);
        }
        String sql="update product set last_restock_time = ? where id = ?";
        return JdbcUtil.executeUpdate(sql,restockTime,productId);
    }
}
